// 作业：建一个用户表（有姓名,年龄,创建时间,修改时间）,
// 使用模块加mysql模块,实现对数据的增删改查 修改时记得，
// 把修改时间改成修改时的时间，能实现分页（选做）
// 能实现分页（选做）,能实现男女个数和总数的统计,
// 能统计出（小于18） 和 18-45 和 45岁以上的人数
let fs=require('fs');
let mysql=require('mysql');
let connection=mysql.createConnection({host:'127.0.0.1',user:'root',password:'root',database:'user_table'});
connection.connect();
//查询操作
connection.query("select * from user",(err,data)=>{
    console.log("--------查询操作-------");
    console.log(err);
    console.log(data);
    console.log(data[0].user_name);
    console.log(data[0].user_age);
})
//新增操作
 let insertSql="insert into user ('user_name','user_age','user_sex','create_time','update_time') value ('青葵',36,'女',null,null)";
 connection.query(insertSql,(err,data)=>{
    console.log("-----新增-----");
    console.log(err);
    console.log(data);
 })
var update=new Date().toLocaleString();

//修改操作
let updateSql="update user set update_time='"+ update +"'where user_id=3";
console.log(updateSql);
connection.query(updateSql,(err,data)=>{
    console.log("-----修改操作-----");
console.log(err);;
console.log(data);
})
//删除操作
var deleteSql="delete from user where user_id=5";
connection.query(deleteSql,(err,data)=>{
    console.log("----删除操作");
    console.log(err);
    console.log(data);
});
let page=1;
let pageSize=5;
//分页固定公式
let sql="select * from user limit "+(page-1)* pageSize + ","+pageSize;
connection.query(sql,(err,data)=>{
    console.log(data);
});

connection.query("select count(*) as num from user where user_sex='男'",(err,date=>{
    console.log(err);
    console.log("男生个数为："+data[0]['num']);
}));
connection.query("select count(*) as num from user where user_sex='女'",(err,data=>{
    console.log(err);
    console.log("女生个数为："+data[0]['num']);
}));
connection.query("select count(*) as num from user",(err,data)=>{
    console.log(err);
    console.log("男生总数为："+data[0]['num']);
});
connection.query("select count(*) as num from user where user_age<18 and user_age<=45",(err,data)=>{
    console.log(err);
    console.log("18-45的个数有："+data[0]['num']);
});
connection.query("select count(*) as num from user where user_age>45",(err,data)=>{
    console.log(err);
    console.log("大于45的个数有："+data[0]['num']);
});
connection.query("select count(*) as num from user where user_age<18",(err,data)=>{
    console.log(err);
    console.log("小于18的个数有："+data[0]['num']);
});
connection.end();